<?php
class Model_Notification extends Model_Template
{
	private $className2;

	public function __construct()
	{
		parent::__construct();
		$this->className = 'Model_Table_NotificationHasProfile';
		$this->className2 = 'Model_Table_Notification';

	}
	
	/////////////////////////////////////////////////////returns all notification types
	public function getAllNotificationTypes()
	{
		//Select all notification type query
		$sqlAllNotificationType = 'SELECT * FROM notification;';
	
		//Prepare statement
		$allNotificationType = Controller_Template::$db->prepareEKADBPDO($sqlAllNotificationType, $this->className2, array('notification_id','notification_type','notification_message'));
	
		//Execute statement
		$allNotificationType->execute();
	
	
		return $allNotificationType->fetchAll();
	}
	
	/////////////////////////////////////////////////////////returns all notifications for one profile
	public function getAllNotifications($target)
	{
		//Select all notification from profile query
		$sqlAllNotificationFromProfile = 'SELECT * FROM Notification_has_Profile WHERE Profile_profile_id= ? ;';
		
		//Prepare statement
		$allNotificationFromProfile = Controller_Template::$db->prepareEKADBPDO($sqlAllNotificationFromProfile, $this->className, array('Notification_notification_id','Profile_profile_id'));
		
		//Execute statement
		$allNotificationFromProfile->execute(array($target));
		
		
		return $allNotificationFromProfile->fetchAll();
	}
	
	
	/////////////////////////////////////////////////////////creates a notification for one profile
	public function createNewNotification($newN)
	{
		//Create new car query
		$sqlCreateNewNotification = 'INSERT INTO notification_has_profile (Notification_notification_id, Profile_profile_id) VALUES (?, ?);';
		
		//Prepare statement
		$createNewNotification = Controller_Template::$db->prepareEKADBPDO($sqlCreateNewNotification, $this->className, array('Notification_notification_id','Profile_profile_id'));
		
		//Execute statement
		$createNewNotification->execute(array($newN[':notification_id'], $newN[':profile_id']));
		
		return $createNewNotification->rowCount();
	}
	
	
/*
	public function deleteNotification($targetProfile, $targetNotification)
	{
		//Create new car query
		$sqlDeleteNotification = 'DELETE FROM notification_has_profile WHERE Profile_profile_id=? AND Notification_notification_id=?;';
		
		//Prepare statement
		$deleteNotification = Controller_Template::$db->prepareEKADBPDO($sqlDeleteNotification, $this->className, array('Notification_notification_id','Profile_profile_id'));
		
		//Execute statement
		$deleteNotification->execute(array($targetProfile,$targetNotification));
		
		$deleteNotification->rowCount();
	}
*/	
	//////////////////////////////////////////////////////////////////////////////delete all notification for one profile
	public function deleteNotificationByProfile($targetProfile)
	{
		//Create new car query
		$sqlDeleteNotification = 'DELETE FROM notification_has_profile WHERE Profile_profile_id=?;';
	
		//Prepare statement
		$deleteNotification = Controller_Template::$db->prepareEKADBPDO($sqlDeleteNotification, $this->className, array('Notification_notification_id','Profile_profile_id'));
	
		//Execute statement
		$deleteNotification->execute(array($targetProfile));
	
		return $deleteNotification->rowCount();
	}
	
	
	//////////////////////////////////////////////////////////////////////////////////updates notification template message
	public function updateNotificationTemplate($notification)
	{
		//Update notification template query
		$sqlUpdateNotificationTemplate = 'UPDATE notification SET notification_message=? WHERE notification_id=?;';
		
		//Prepare statement
		$updateNotificationTemplate = Controller_Template::$db->prepareEKADBPDO($sqlUpdateNotificationTemplate, $this->className2, array('notification_id','notification_type','notification_message'));
		
		//Execute statement
		$updateNotificationTemplate->execute(array($notification[':notification_message'],$notification[':notification_id']));
		
		return $updateNotificationTemplate->rowCount();
		
	}
	
	///////////////////////////////////////////////////////////////////////return a list of e-mails with appointment notification enabled in profile and with appointment due in 2 days
	public function appointmentNotificationEmailList()
	{
		$md = strtotime(date("Ymd"));
		
		$targetDate=date("Ymd", mktime(date('h',$md),date('i',$md), date('s',$md), date('m',$md),date('d',$md)+2, date('Y',$md)));
		//Select all e-mail with appointment notification enabled and with appointment due in 2 days query
		$sqlSelectAppointmentEmails='SELECT Person.email, Person.first_name, Person.last_name
									FROM Person
									INNER JOIN Profile ON Person.person_id = Profile.Person_person_id
									INNER JOIN Appointment ON Profile.profile_id = Appointment.Profile_profile_id
									INNER JOIN Notification_has_Profile ON Profile.profile_id = Notification_has_Profile.Profile_profile_id
									WHERE Notification_has_Profile.Notification_notification_id =1
									AND Appointment.date = ?;';
		
		$selectAppointmentEmails=Controller_Template::$db->prepare($sqlSelectAppointmentEmails);
		
		$selectAppointmentEmails->execute(array($targetDate));
		
		return $selectAppointmentEmails->fetchAll();
		
	}
	
	
	//////////////////////////////////////////////////////////////////////return a list of e-mails with maintenence notification enabled in profile and with maintenence due
	public function maintenenceNotificationEmailList()
	{
		$md = strtotime(date("Ymd"));
		
		$targetDate=date("Ymd", mktime(date('h',$md),date('i',$md), date('s',$md), date('m',$md)-4,date('d',$md), date('Y',$md)));
		//Select all e-mail with appointment notification enabled and with appointment due in 2 days query
		$sqlSelectMaintenenceEmails='SELECT Person.email, Person.first_name, Person.last_name
									FROM Person
									INNER JOIN Profile ON Person.person_id = Profile.Person_person_id
									INNER JOIN Car ON Profile.profile_id = Car.Profile_profile_id
									INNER JOIN Notification_has_Profile ON Profile.profile_id = Notification_has_Profile.Profile_profile_id
									WHERE Notification_has_Profile.Notification_notification_id =2
									AND Car.last_oil_change_date != 00000000
									AND Car.last_oil_change_date = ?;';
		
		$selectMaintenenceEmails=Controller_Template::$db->prepare($sqlSelectMaintenenceEmails);
		
		$selectMaintenenceEmails->execute(array($targetDate));
		
		return $selectMaintenenceEmails->fetchAll();
	}
	
	////////////////////////////////////////////////////////////////////////return a list of e-mails with promo notification enabled in profiles
	public function promotionalNotificationEmailList()
	{
		//Select all e-mail with pomo notification enabled query
		$sqlSelectPromoEmails="SELECT Person.email, Person.first_name, Person.last_name
								FROM Person
								INNER JOIN Profile ON Person.person_id = Profile.Person_person_id
								INNER JOIN Notification_has_Profile ON Profile.profile_id = Notification_has_Profile.Profile_profile_id
								WHERE Notification_has_Profile.Notification_notification_id=3;";
		
		$selectPromoEmails=Controller_Template::$db->prepare($sqlSelectPromoEmails);
		
		$selectPromoEmails->execute();
		
		return $selectPromoEmails->fetchAll();
	}
	
	///////////////////////////////////////////////////////////////////////return a list of phone number with appointment notification enabled in profile and with appointment due in 2 days
	public function appointmentNotificationPhoneList()
	{
		$md = strtotime(date("Ymd"));
	
		$targetDate=date("Ymd", mktime(date('h',$md),date('i',$md), date('s',$md), date('m',$md),date('d',$md)+2, date('Y',$md)));
		//Select all e-mail with appointment notification enabled and with appointment due in 2 days query
		$sqlSelectAppointmentEmails='SELECT Person.cell_phone_carrier, Person.first_name, Person.last_name
									FROM Person
									INNER JOIN Profile ON Person.person_id = Profile.Person_person_id
									INNER JOIN Appointment ON Profile.profile_id = Appointment.Profile_profile_id
									INNER JOIN Notification_has_Profile ON Profile.profile_id = Notification_has_Profile.Profile_profile_id
									WHERE Notification_has_Profile.Notification_notification_id =1
									AND Appointment.date = ?;';
	
		$selectAppointmentEmails=Controller_Template::$db->prepare($sqlSelectAppointmentEmails);
	
		$selectAppointmentEmails->execute(array($targetDate));
	
		return $selectAppointmentEmails->fetchAll();
	
	}
	
	
	//////////////////////////////////////////////////////////////////////return a list of phone number with maintenence notification enabled in profile and with maintenence due
	public function maintenenceNotificationPhoneList()
	{
		$md = strtotime(date("Ymd"));
	
		$targetDate=date("Ymd", mktime(date('h',$md),date('i',$md), date('s',$md), date('m',$md)-4,date('d',$md), date('Y',$md)));
		//Select all e-mail with appointment notification enabled and with appointment due in 2 days query
		$sqlSelectMaintenenceEmails='SELECT Person.cell_phone_carrier, Person.first_name, Person.last_name
									FROM Person
									INNER JOIN Profile ON Person.person_id = Profile.Person_person_id
									INNER JOIN Car ON Profile.profile_id = Car.Profile_profile_id
									INNER JOIN Notification_has_Profile ON Profile.profile_id = Notification_has_Profile.Profile_profile_id
									WHERE Notification_has_Profile.Notification_notification_id =2
									AND Car.last_oil_change_date != 00000000
									AND Car.last_oil_change_date = ?;';
	
		$selectMaintenenceEmails=Controller_Template::$db->prepare($sqlSelectMaintenenceEmails);
	
		$selectMaintenenceEmails->execute(array($targetDate));
	
		return $selectMaintenenceEmails->fetchAll();
	}
	
	////////////////////////////////////////////////////////////////////////return a list of phone number with promo notification enabled in profiles
	public function promotionalNotificationPhoneList()
	{
		//Select all e-mail with pomo notification enabled query
		$sqlSelectPromoEmails="SELECT Person.cell_phone_carrier, Person.first_name, Person.last_name
								FROM Person
								INNER JOIN Profile ON Person.person_id = Profile.Person_person_id
								INNER JOIN Notification_has_Profile ON Profile.profile_id = Notification_has_Profile.Profile_profile_id
								WHERE Notification_has_Profile.Notification_notification_id=3;";
	
		$selectPromoEmails=Controller_Template::$db->prepare($sqlSelectPromoEmails);
	
		$selectPromoEmails->execute();
	
		return $selectPromoEmails->fetchAll();
	}
}